/**
 *
 */
package com.zhijian.medical.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;

public class Xlsx2Xls {

    private File path = new File("c:/Integration-TestCases.xls");
    private ArrayList<File> inputFiles = new ArrayList<File>();

    private int lastColumn = 0;
    private HashMap<Integer, HSSFCellStyle> styleMap = new HashMap();

    public void getInputFiles() {
        String call = "getInputFiles ";
        if (this.path.isFile()) {
            if (this.path.getAbsolutePath().endsWith(".xls")
                    && !new File(this.path.getAbsolutePath() + "x").exists())
                this.inputFiles.add(this.path);
            else {
                //System.out.println("Datei endet nicht mit .xls oder XLSX-Datei existiert bereits");
            }
        } else {
            for (File f : this.path.listFiles((dir, name) -> {
                if (name.endsWith(".xls")) {
                    return true;
                }
                return false;
            })) {
                if (!new File(f.getAbsoluteFile() + "x").exists()) {
                    this.inputFiles.add(f);
                }
            }
        }
        //System.out.println(call + "Dateien gefunden: " + this.inputFiles.size());
        //System.out.println(call + "abgeschlossen");
    }

    private HSSFWorkbook getWorkBook(File f) throws FileNotFoundException, IOException {
        //System.out.println("getWorkBook lese " + f.getAbsolutePath());
        POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(
                new FileInputStream(f)));
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        //System.out.println("getWorkBook abgeschlossen");
        return workbook;
    }

    public void transformXSSF(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew) {
        String call = "transform ";
        //System.out.println(call + "Workbook");
        HSSFSheet sheetNew;
        XSSFSheet sheetOld;
        //TODO::workbookNew.setForceFormulaRecalculation(workbookOld.getForceFormulaRecalculation());
        // workbookNew.setHidden(workbookOld.isHidden()); //ST@03.05.2012 -
        // von Apache noch nicht implementiert
        workbookNew.setMissingCellPolicy(workbookOld.getMissingCellPolicy());

        for (int i = 0; i < workbookOld.getNumberOfSheets(); i++) {
            sheetOld = workbookOld.getSheetAt(i);
            sheetNew = workbookNew.getSheet(sheetOld.getSheetName());
            //System.out.println(call + "Sheet Name: " + sheetOld.getSheetName());
            sheetNew = workbookNew.createSheet(sheetOld.getSheetName());
            this.transform(workbookOld, workbookNew, sheetOld, sheetNew);
        }
        //System.out.println(call + "Styles size: "        + this.styleMap.size());
        //System.out.println(call + "abgeschlossen");
    }


    public void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew, XSSFSheet sheetOld, HSSFSheet sheetNew) {
        //System.out.println("transform Sheet");

        sheetNew.setDisplayFormulas(sheetOld.isDisplayFormulas());
        sheetNew.setDisplayGridlines(sheetOld.isDisplayGridlines());
        sheetNew.setDisplayGuts(sheetOld.getDisplayGuts());
        sheetNew.setDisplayRowColHeadings(sheetOld.isDisplayRowColHeadings());
        sheetNew.setDisplayZeros(sheetOld.isDisplayZeros());
        sheetNew.setFitToPage(sheetOld.getFitToPage());
        //
        //TODO::sheetNew.setForceFormulaRecalculation(sheetOld.getForceFormulaRecalculation());
        sheetNew.setHorizontallyCenter(sheetOld.getHorizontallyCenter());
        sheetNew.setMargin(Sheet.BottomMargin,
                sheetOld.getMargin(Sheet.BottomMargin));
        sheetNew.setMargin(Sheet.FooterMargin,
                sheetOld.getMargin(Sheet.FooterMargin));
        sheetNew.setMargin(Sheet.HeaderMargin,
                sheetOld.getMargin(Sheet.HeaderMargin));
        sheetNew.setMargin(Sheet.LeftMargin,
                sheetOld.getMargin(Sheet.LeftMargin));
        sheetNew.setMargin(Sheet.RightMargin,
                sheetOld.getMargin(Sheet.RightMargin));
        sheetNew.setMargin(Sheet.TopMargin, sheetOld.getMargin(Sheet.TopMargin));
        sheetNew.setPrintGridlines(sheetNew.isPrintGridlines());
        sheetNew.setRightToLeft(sheetNew.isRightToLeft());
        sheetNew.setRowSumsBelow(sheetNew.getRowSumsBelow());
        sheetNew.setRowSumsRight(sheetNew.getRowSumsRight());
        sheetNew.setVerticallyCenter(sheetOld.getVerticallyCenter());

        HSSFRow rowNew;
        for (Row row : sheetOld) {
            rowNew = sheetNew.createRow(row.getRowNum());
            if (rowNew != null)
                this.transform(workbookOld, workbookNew, (XSSFRow) row, rowNew);
        }

        for (int i = 0; i < this.lastColumn; i++) {
            sheetNew.setColumnWidth(i, sheetOld.getColumnWidth(i));
            sheetNew.setColumnHidden(i, sheetOld.isColumnHidden(i));
        }

        for (int i = 0; i < sheetOld.getNumMergedRegions(); i++) {
            CellRangeAddress merged = sheetOld.getMergedRegion(i);
            sheetNew.addMergedRegion(merged);
        }
    }


    public void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew, XSSFRow rowOld, HSSFRow rowNew) {
        HSSFCell cellNew;
        rowNew.setHeight(rowOld.getHeight());
        //TODO::if (rowOld.getRowStyle() != null) {
        /*Integer hash = rowOld.getRowStyle().hashCode();
            if (!this.styleMap.containsKey(hash))
                this.transform(workbookOld,workbookNew,hash, (XSSFCellStyle)rowOld.getRowStyle(),(HSSFCellStyle)workbookNew.createCellStyle());
            rowNew.setRowStyle(this.styleMap.get(hash));
        }*/
        for (Cell cell : rowOld) {
            cellNew = rowNew.createCell(cell.getColumnIndex(),
                    cell.getCellType());
            if (cellNew != null)
                this.transform(workbookOld, workbookNew, (XSSFCell) cell, cellNew);
        }
        this.lastColumn = Math.max(this.lastColumn, rowOld.getLastCellNum());
    }


    public void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew, XSSFCell cellOld, HSSFCell cellNew) {
        cellNew.setCellComment(cellOld.getCellComment());

        Integer hash = cellOld.getCellStyle().hashCode();
        if (this.styleMap != null && !this.styleMap.containsKey(hash)) {
            this.transform(workbookOld, workbookNew, hash, cellOld.getCellStyle(), (HSSFCellStyle) workbookNew.createCellStyle());
        }
        cellNew.setCellStyle(this.styleMap.get(hash));

        switch (cellOld.getCellType()) {
            case BLANK:
                break;
            case BOOLEAN:
                cellNew.setCellValue(cellOld.getBooleanCellValue());
                break;
            case ERROR:
                cellNew.setCellValue(cellOld.getErrorCellValue());
                break;
            case FORMULA:
                cellNew.setCellValue(cellOld.getCellFormula());
                break;
            case NUMERIC:
                cellNew.setCellValue(cellOld.getNumericCellValue());
                break;
            case STRING:
                cellNew.setCellValue(cellOld.getStringCellValue());
                break;
            default:
                System.out.println("transform: Unbekannter Zellentyp "
                        + cellOld.getCellType());
        }
    }


    public void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew, Integer hash, XSSFCellStyle styleOld,
                          HSSFCellStyle styleNew) {
        styleNew.setAlignment(styleOld.getAlignment());
        styleNew.setBorderBottom(styleOld.getBorderBottom());
        styleNew.setBorderLeft(styleOld.getBorderLeft());
        styleNew.setBorderRight(styleOld.getBorderRight());
        styleNew.setBorderTop(styleOld.getBorderTop());
        styleNew.setDataFormat(this.transform(workbookOld, workbookNew, styleOld.getDataFormat()));
        styleNew.setFillBackgroundColor(styleOld.getFillBackgroundColor());
        styleNew.setFillForegroundColor(styleOld.getFillForegroundColor());
        styleNew.setFillPattern(styleOld.getFillPattern());
        styleNew.setFont(this.transform(workbookNew, (XSSFFont) styleOld.getFont()));
        styleNew.setHidden(styleOld.getHidden());
        styleNew.setIndention(styleOld.getIndention());
        styleNew.setLocked(styleOld.getLocked());
        styleNew.setVerticalAlignment(styleOld.getVerticalAlignment());
        styleNew.setWrapText(styleOld.getWrapText());
        this.styleMap.put(hash, styleNew);
    }

    private short transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew, short index) {
        DataFormat formatOld = workbookOld.createDataFormat();
        DataFormat formatNew = workbookNew.createDataFormat();
        return formatNew.getFormat(formatOld.getFormat(index));
    }


    private HSSFFont transform(HSSFWorkbook workbookNew, XSSFFont fontOld) {
        HSSFFont fontNew = workbookNew.createFont();
        fontNew.setBold(fontOld.getBold());
        fontNew.setCharSet(fontOld.getCharSet());
        fontNew.setColor(fontOld.getColor());
        fontNew.setFontName(fontOld.getFontName());
        fontNew.setFontHeight(fontOld.getFontHeight());
        fontNew.setItalic(fontOld.getItalic());
        fontNew.setStrikeout(fontOld.getStrikeout());
        fontNew.setTypeOffset(fontOld.getTypeOffset());
        fontNew.setUnderline(fontOld.getUnderline());
        return fontNew;
    }

    public static void main(String[] args) throws Exception {
        Xlsx2Xls xls = new Xlsx2Xls();
        //xls.transform();
        InputStream isXlsx = new FileInputStream("c:/OTD.xlsx");
        XSSFWorkbook workbookOld = new XSSFWorkbook(isXlsx);

        String dest = "c:/OTD1.xls";

        FileOutputStream out = new FileOutputStream(dest);
        HSSFWorkbook workbookNew = new HSSFWorkbook();
        xls.transformXSSF(workbookOld, workbookNew);
        workbookNew.write(out);

        out.close();

        //    InputStream isXls = new FileInputStream("c:/OTD1.xls");
        //    HSSFWorkbook workbookNew     = new HSSFWorkbook(isXls);


    }
}